﻿IF EXISTS (SELECT * FROM sysobjects WHERE type = 'P' AND name = 'usp_UPDMS_COM_FILE_Delete')
	BEGIN
		DROP Procedure usp_UPDMS_COM_FILE_Delete
	END
GO

SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
GO
/******************************************************************************
**	Name : usp_UPDMS_COM_FILE_Delete
**	Desc : 첨부파일을 삭제한다.
**	Test Exec Query : Exec usp_UPDMS_COM_FILE_Delete ''
**	Called by : Com_Dac_UPDMS_COM_FILE.cs
**	Program ID : Anywhere
**	Auth : 송시명
**	Date : 2013-06-14
*******************************************************************************
**	Change History
*******************************************************************************
**	Date:		Author:		Description:
**	--------	--------	---------------------------------------
**	
*******************************************************************************/
CREATE PROC [dbo].[usp_UPDMS_COM_FILE_Delete]
@ls_xml nvarchar(max) = '<UPDMS></UPDMS>' --루트노드
AS
BEGIN

DECLARE @iXmlHandle int
EXEC sp_xml_preparedocument @iXmlHandle OUTPUT, @ls_xml

DELETE FROM UPDMS_COM_FILE
 WHERE File_Id IN (
         SELECT ucf.File_Id
           FROM UPDMS_COM_FILE AS ucf WITH(NOLOCK)
           JOIN (
                SELECT Activity_Id,
                       Activity_Cd
                  FROM OPENXML(@iXmlHandle, '/UPDMS/UPDMS_COM_FILE', 2)
                  WITH ( Activity_Id int,
                         Activity_Cd varchar(10)
                       ) x
                ) y
             ON ucf.Activity_Id = y.Activity_Id
                AND ucf.Activity_Cd = y.Activity_Cd
       )

--메모리 해제
EXEC sp_xml_removedocument @iXmlHandle
END

GO

/*
--Test Exec Query--

DECLARE @ls_xml nvarchar(max) 
SET @ls_xml='<UPDMS><UPDMS_COM_FILE><Activity_Id>112</Activity_Id><Activity_Cd>Ref1012m</Activity_Cd></UPDMS_COM_FILE><UPDMS_COM_FILE><Activity_Id>113</Activity_Id><Activity_Cd>Ref1012m</Activity_Cd></UPDMS_COM_FILE></UPDMS>'

EXEC usp_UPDMS_COM_FILE_Delete @ls_xml

*/